package web.pio.test;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.*;

public class XlsMain {

    public static void main(String[] args) throws IOException {
        XlsMain xlsMain = new XlsMain();

        xlsMain.readXls();
    }

    private void readXls() throws IOException {
        InputStream is = new FileInputStream("D:\\[工程临时]\\[中学选课与学分鉴定]\\价格表整理11.xls");
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }

            // 循环行Row
            for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }

                // 循环列Cell
                for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
                    HSSFCell hssfCell = hssfRow.getCell(cellNum);
                    if (hssfCell == null) {
                        continue;
                    }

                    System.out.print("    " + getValue(hssfCell));
                }
                System.out.println();
            }
        }
    }

    @SuppressWarnings("static-access")
    private Object getValue(HSSFCell hssfCell) {
        switch (hssfCell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING: // 字符串
                return hssfCell.getStringCellValue();
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                return hssfCell.getBooleanCellValue();
            case HSSFCell.CELL_TYPE_FORMULA: // 公式  按照算出来的结果取值
                return hssfCell.getNumericCellValue();
//            case HSSFCell.CELL_TYPE_BLANK: // 空值
//                return null;
//            case HSSFCell.CELL_TYPE_ERROR: // 故障
//                return null;
            case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
                Object value = null;
                if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
                    //  如果是date类型则 ，获取该cell的date值
                    value = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()).toString();
                } else { // 纯数字
                    value = hssfCell.getNumericCellValue();
                }
                return value;
        }
        return null;
    }
}

